package Dao;

import entity.Mail;
import utils.JdbcUtils_DBCP;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author DONG
 */
public class UserDaoImpl implements UserDao {
    @Override
    public String login(String id) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
           String sql = "SELECT `password` FROM `user` WHERE    UId=?;";
            //预编译sql 先写sql 但是不执行
            st = conn.prepareStatement(sql);
            //手动给参数赋值

            st.setString(1, id);


            //以上填充完毕 然后执行
            re = st.executeQuery();
            if (re.next()){
                return re.getString("Password");
            }else {
                return null;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
        return null;
    }

    @Override
    public boolean register(String uId, String password, String idCard, String sex,
                            int age, String address, String uName, String telephone) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
            String sql = "insert  into `user`(`uid`,`password`,`Idcard`,`sex`,`age`,`address`,`uname`,`Telephone`) values (?,?,?,?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            //手动给参数赋值

            st.setString(1, uId);
            st.setString(2, password);
            st.setString(3, idCard);
            st.setString(4, sex);
            st.setInt(5, age);
            st.setString(6, address);
            st.setString(7, uName);
            st.setString(8, telephone);
            //以上填充完毕 然后执行
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("用户注册：插入成功");
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            System.out.println("SQLException");
            return false;
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
    }

    @Override
    public boolean modifyUser(String uId, String password, String idCard, String sex, int age, String address, String uName, String telephone) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
            String sql = "    UPDATE `user` SET `password`=?,`Idcard`=?,`sex`=?,`age`=?,`address`=?,`uname`=?,`Telephone`=? Where uid=?;\n";
            st = conn.prepareStatement(sql);
            //手动给参数赋值

            st.setString(8, uId);
            st.setString(1, password);
            st.setString(2, idCard);
            st.setString(3, sex);
            st.setInt(4, age);
            st.setString(5, address);
            st.setString(6, uName);
            st.setString(7, telephone);
            //以上填充完毕 然后执行
            int i = st.executeUpdate();
            if (i > 0) {
                System.out.println("用户信息修改成功");
                return true;
            }else {
                return false;
            }
        } catch (SQLException e) {
            System.out.println("SQLException");
            return false;
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
    }

    @Override
    public List<Mail> queryMail(String uId) {
        List<Mail> Mails = new ArrayList<Mail>();
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
            String sql = "select * from mail Where uid = ? ;";
            st = conn.prepareStatement(sql);
            st.setString(1,uId);
            re = st.executeQuery();
            while (re.next()){
                String mid = re.getString("mid");
                String mName = re.getString("mname");
                String arrivalTime = re.getString("arrivaltime");
                String CompletionTime = re.getString("Completiontime");
                String mailState=re.getString("mailstate");
                Mail mail = new Mail(mid,uId,mName, arrivalTime, CompletionTime, mailState);
                Mails.add(mail);
            }
            return Mails;
        } catch (SQLException e) {
            System.out.println("SQLException");
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
        return null;
    }

    @Override
    public String queryAddress(String uId) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
            String sql = "SELECT `address` FROM `user` WHERE   UId=?;";
            //预编译sql 先写sql 但是不执行
            st = conn.prepareStatement(sql);
            //手动给参数赋值

            st.setString(1, uId);


            //以上填充完毕 然后执行
            re = st.executeQuery();
            if (re.next()){
                return re.getString("address");
            }else {
                return null;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
        return null;
    }

    @Override
    public String queryTelephone(String uId) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //?是占位符 代替参数
            String sql = "SELECT `Telephone` FROM `user` WHERE   UId=?;";
            //预编译sql 先写sql 但是不执行
            st = conn.prepareStatement(sql);
            //手动给参数赋值

            st.setString(1, uId);


            //以上填充完毕 然后执行
            re = st.executeQuery();
            if (re.next()){
                return re.getString("Telephone");
            }else {
                return null;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn, st, null);
        }
        return null;
    }
}
